﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Windows.Forms;

namespace StudentMgmt.DBUtility
{
    public abstract class SqlHelper
    {
        //获取数据库连接字符串，其属于静态变量且只读，项目中所有文档可以直接使用，但不能修改
        //Gets the ConnectionStringsSection data for the current application's default configuration.
        public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;

        public static void RunSQL(string strsql, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            /* using(对象){
                需要释放对象时,必须加上个Dispose()或者Close(); 但是使用using 你可以不使用Dispose()
                只需要在创建他对象的时候前面加个using 就可以了...
                大扩号 是他有效的范围..
                也就是说. 到了 } 之后. 你拿到的就是个关闭的对象了.
                }在这个范围内使用这个对象 ，超过这个范围 马上释放这个对象*/
            using (SqlConnection conn = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, conn, strsql, commandParameters);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }

        public static int RunSQLReturnValue(string strsql, params SqlParameter[] commandParameters)
        { /*当需要传递多个参数，或参数个数不定的时候，就可以用params类型的参数。*/
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, connection, strsql, commandParameters);
                int val = (int)cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static string RunSQLReturnString(string strsql, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, connection, strsql, commandParameters);
                string val = (string)cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }


        public static int RunSQLReturnDataTable(string strsql, out DataTable objTable, params SqlParameter[] commandParameters)
        {
            objTable = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            int val = 0;

            using (SqlConnection conn = new SqlConnection(ConnectionStringProfile))
            {
                PrepareCommand(cmd, conn, strsql, commandParameters);
                try
                {
                    da.Fill(objTable);
                    cmd.Parameters.Clear();
                    val = 1;
                }
                catch (Exception Exp)
                {
                    MessageBox.Show(Exp.GetType().ToString());
                    cmd.Parameters.Clear();
                    val = 0;
                }
                return val;
            }
        }


        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}
